package cc.bookcloud.tool.ocr.common.excel;

import org.springframework.context.EnvironmentAware;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;

import java.util.*;

@Component
public class TableConvertDbScriptEngine implements EnvironmentAware {

    private static final String ln = "\r\n";

    private static final String tn = "\t";
    public static final String NO = "否";
    public static final String YES = "是";
    public static final String ADD_TABLE = "增加";
    public static final String UPDATE_TABLE = "修改";
    public static final String APPEND_COLUMN = "追加";

    private static Environment environment;

    private static final String link = "LOGGING\n" +
            "NOCOMPRESS\n" +
            "PCTFREE 10\n" +
            "INITRANS 1\n" +
            "STORAGE (\n" +
            "  INITIAL 65536\n" +
            "  NEXT 1048576\n" +
            "  MINEXTENTS 1\n" +
            "  MAXEXTENTS 2147483645\n" +
            "  BUFFER_POOL DEFAULT\n" +
            ")\n" +
            "PARALLEL 1\n" +
            "NOCACHE\n" +
            "DISABLE ROW MOVEMENT\n" +
            ";";

    // 存储数据
    private static ThreadLocal<List> tl = new ThreadLocal<>();


    public static void addTable(TableMeta tableMeta) {
        List list = tl.get();
        if (list == null) {
            list = new ArrayList();
            tl.set(list);
        }
        // 地址引用
        list.add(tableMeta);
    }


    public static List<StringBuffer> convert(String dbType) {
        // 根据数据库类型，生成不同的脚本    tl的数据在拦截器里清空
        Set<TableMeta> tableMetaList = new LinkedHashSet<TableMeta>(tl.get());
        List<StringBuffer> tableSqlList = new ArrayList<>();
        int count = 0;
        for (TableMeta tableMeta : tableMetaList) {
            StringBuffer buffer = new StringBuffer();
            String tableName = tableMeta.getTableName();
            String opType = tableMeta.getOpType();
            List<ColumnProperty> propertyList = tableMeta.getPropertyList();
            //
            if (ADD_TABLE.equals(opType)) {
                StringBuffer commentBuffer = new StringBuffer();
                buffer.append("-- DDL【创建表】：").append(tableName).append(ln);
                buffer.append("CREATE TABLE ").append(tableName).append("(").append(ln);
                for (ColumnProperty property : propertyList) {
                    // 数据库字段
                    String dataType = property.getDataType();
                    String columnType = environment.getProperty(dbType + "." + dataType);
                    buffer.append(tn).append(property.getColName()).append(" ").append(columnType);
                    //时间和integer类型不加长度
                    if (!("DATE".equalsIgnoreCase(columnType) || "INTEGER".equalsIgnoreCase(columnType) || "BLOB".equalsIgnoreCase(columnType))) {
                        buffer.append("(").append(property.getDataLen()).append(")");
                    }
                    if (NO.equals(property.getAllowIsNull())) {
                        buffer.append(" NOT NULL ");
                    }
                    if (YES.equals(property.getPrimary())) {
                        buffer.append(" PRIMARY KEY ");
                    }
                    if (count != propertyList.size() - 1) {
                        buffer.append(",");
                    }
                    buffer.append(ln);
                    commentBuffer.append("COMMENT ON COLUMN").append(" ").append(tableName).append(".").append(property.getColName())
                            .append(" IS ").append("'").append(property.getRemark()).append("'").append(";").append(ln);
                    count++;
                }
                count = 0;
                buffer.append("ORACLE".equals(dbType) ? ")" : ");").append(ln);
                buffer = "ORACLE".equals(dbType) ? buffer.append(link).append(ln).append(commentBuffer) : buffer.append(commentBuffer);
            } else if (UPDATE_TABLE.equals(opType)) {
                buffer.append("-- DDL【更新表】：").append(tableName).append(ln);
                //alter table tablename modify (column datatype [default value][null/not null],….)
                for (ColumnProperty property : propertyList) {
                    buffer.append("ALTER TABLE ").append(tableName).append(" MODIFY (").append(property.getColName());
                    doAdditional(dbType, buffer, tableName, property);
                }
            } else if (APPEND_COLUMN.equals(opType)) {
                buffer.append("-- DDL【追加字段】：").append(tableName).append(ln);
                for (ColumnProperty property : propertyList) {
                    buffer.append("ALTER TABLE ").append(tableName).append(" ADD (").append(property.getColName());
                    //拼接字段信息
                    doAdditional(dbType, buffer, tableName, property);
                }
            }
            tableSqlList.add(buffer);
        }
        return tableSqlList;
    }


    private static void doAdditional(String dbType, StringBuffer buffer, String tableName, ColumnProperty property) {
        String columnType = environment.getProperty(dbType + "." + property.getDataType());
        buffer.append(" ").append(columnType);
        if (!("DATE".equals(columnType) || "INTEGER".equals(columnType))) {
            buffer.append(" (").append(property.getDataLen()).append(")");
        }
        buffer.append(")").append(";").append(ln);
        buffer.append("COMMENT ON COLUMN ").append(tableName).append(".").append(property.getColName())
                .append(" IS ").append("'").append(property.getRemark())
                .append("'").append(";").append(ln);
    }

    public static ThreadLocal<List> getTl() {
        return tl;
    }

    @Override
    public void setEnvironment(Environment environment) {
        this.environment = environment;
    }

}
